package com.admin.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.admin.bean.ConsumeOrderBean;

@Repository
public class ConsumeOrderDao {

	private static final Logger log = Logger.getLogger(ConsumeOrderDao.class);
	
	@Autowired private JdbcTemplate jdbcTemplate;
	@Autowired private QueryRunner queryRunner;
	
	/**
	 * 添加新的订单记录
	 * @param orderBean
	 * @return
	 */
	public int insertOrder(ConsumeOrderBean orderBean){
		String sql = "insert into consume_order(productId,userId,moblieTypeId,consume,billingId,status,consumeType,updateTime)values(?,?,?,?,?,?,?,now())";
		Object[] params = {orderBean.getProductId(),orderBean.getUserId(),orderBean.getMoblieTypeId(),orderBean.getConsume(),
				orderBean.getBillingId(),orderBean.getStatus(),orderBean.getConsumeType()};
		log.info("sql:"+sql);
		log.info("params:"+params.toString());
		try{
			return jdbcTemplate.update(sql, params);
		}catch(DataAccessException e){
			e.printStackTrace();
			return 0;
		}
	}
	
	/**
	 * 查询该用户某计费点的消费记录
	 * @param userId
	 * @param billingId
	 * @param consumeType
	 * @return
	 * @throws SQLException
	 */
	public ConsumeOrderBean findByCondition(int userId,int billingId,int consumeType) throws SQLException{
		String sql = "select * from consume_order where userId = ? and billingId = ? and consumeType=? and status = 0";
		Object[] params = {userId,billingId,consumeType};
		return queryRunner.query(sql, new BeanHandler<ConsumeOrderBean>(ConsumeOrderBean.class), params);
	}
	
	/**
	 * 更新用户消费记录
	 * @param orderId
	 * @param consume
	 * @return
	 */
	public int updateConsume(int orderId,int consume){
		String sql = "update consume_order set consume=consume+? where orderId = ?";
		log.info("sql:"+sql);
		log.info("params:"+orderId+","+consume);
		try{
			return jdbcTemplate.update(sql, new Object[]{consume,orderId});
		}catch(DataAccessException e){
			e.printStackTrace();
			return 0;
		}
	}
	
	/**
	 * 查询订单总数
	 * @param telPhone
	 * @return
	 */
	public int findCount(String telPhone,int status){
		String sql = "select count(0) from consume_order o,mobile_user u where o.userId = u.userId and o.status=?";
		if(StringUtils.isNotEmpty(telPhone)){
			sql += " and u.telPhone=?";
			return jdbcTemplate.queryForInt(sql,new Object[]{status,telPhone});
		}else{
			return jdbcTemplate.queryForInt(sql,status);
		}
	}
	
	/**
	 * 根据条件分页查询
	 * @param telPhone
	 * @param status
	 * @param start
	 * @param pageSize
	 * @return
	 * @throws SQLException
	 */
	public List<ConsumeOrderBean> findByCondition(String telPhone,int status ,int start,int pageSize) throws SQLException{
		StringBuffer sql = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		params.add(status);
		sql.append("select o.*,u.telPhone,b.billingName from consume_order o,mobile_user u,billing b where o.userId = u.userId and o.billingId=b.billingId and o.status=?");
		if(StringUtils.isNotEmpty(telPhone)){
			sql.append(" and u.telPhone=?");
			params.add(telPhone);
		}
		sql.append(" order by o.orderId desc limit ?,?");
		params.add(start);
		params.add(pageSize);
		return queryRunner.query(sql.toString(), new BeanListHandler<ConsumeOrderBean>(ConsumeOrderBean.class), params.toArray());
	}
	
	/**
	 * 
	  * @Title: delByUserId
	  * @Description:删除用户的消费记录 
	  * @param @param userId
	  * @param @return
	  * @return int
	  * @throws
	 */
	public int delByUserId(int userId){
		String sql = "delete from consume_order where userId = ?";
		return jdbcTemplate.update(sql,userId);
	}
	
	/**
	  * @Title: findCountByUserId
	  * @Description:根据用户查询消费次数 
	  * @param userId
	  * @return int
	  * @throws
	 */
	public int findCountByUserId(int userId){
		String sql = "select count(0) from consume_order where userId=?";
		return jdbcTemplate.queryForInt(sql, userId);
	}
	
	/**
	 * 删除该消费记录
	 * @param orderId
	 * @return
	 */
	public int delById(int orderId){
		String sql = "delete from consume_order where orderId = ?";
		return jdbcTemplate.update(sql, orderId);
	}
}
